package db;
import java.sql.*;
import java.util.ArrayList;

import model.Customer;

/**
 * DBEmployee.java
 * @author kbh
 * @version 5. oktober 2006
 */
public class DBCustomer {
    private  Connection con;
    /** Creates a new instance 
     * of DBCustomer */
    public DBCustomer() {
      con = DBConnection.getInstance().getDBcon();
    }
  //Implements the methods from the interface
    // get all Customers
    public ArrayList<Customer> getAllCustomers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one Customer with the phoneNo
    public Customer findCustomer(String custPhoneNo, boolean retriveAssociation)
    {   String wClause = "  phoneNo = '" + custPhoneNo + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    //find one Customer having the fname
    public Customer searchCustomerFname(String attValue, boolean retriveAssociation)
    {
        String wClause = "fname like '%" + attValue + "%'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one Customer having the lname
    public Customer searchCustomerLname(String attValue, boolean retriveAssociation)
    {
        String wClause = "lName = '" + attValue + "'";
        System.out.println("SearchCustomer " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one Customer having the phoneNo
    public Customer searchCustomerPhoneNo(String attValue, boolean retriveAssociation)
    {
        String wClause = "cTelephoneNo = '" + attValue + "'";
        System.out.println("Search Customer: " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
    //insert a new Customer
    public int insertCustomer(Customer customer)
    {  
      
       int rc = -1;
	   String query="INSERT INTO Customer(fname, lname, address,zipCity,phoneNo)  VALUES('"+
	   customer.getFname()  	+ "','"  +
	   customer.getlname()  	+ "','"  +
       customer.getAddress() 	+ "',"  +
       customer.getZipCode() 	+ ",'"  +
       customer.getPhoneNo() 	+ "')";
           System.out.println(query);
                
      System.out.println("insert : " + query);		  					
      try{ // insert new Customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try	
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in Customer db: "+ex);
	   }
	   return(rc);
	}
	
	public int updateCustomer(Customer customer)
	{
		Customer cusObj  = customer;
		int rc=-1;
	  
		String query="UPDATE Customer SET "	+
		 "fname ='"+ cusObj.getFname()		+ "',"+
		 "lname ='"+ cusObj.getlname()  	+ "'," +
		 "address ='"+ cusObj.getAddress()	+ "', " +
		 "zipCity ="+    cusObj.getZipCode()    + " " +
		 " WHERE phoneNo = '"+ cusObj.getPhoneNo() + "'";
                
                System.out.println("Update query:" + query);
  		try{ // update Customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
                      
	 	 	stmt.close();
		}//slut try	
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in Customer db: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(String phoneNo)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM Customer WHERE phoneNo = '" +
	  		phoneNo + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
        //michWere is used whenever we want to select more than one employee
	
	 
	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the Customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
			int snr=0;
			while( results.next() ){
				Customer cusObj = new Customer();
			 cusObj = buildCustomer(results);	
                         list.add(cusObj);	
			}//end while
                        // if retrieveAssociation is true, the supervisor and department
                        // has to be selected for each object in the list
			stmt.close();
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one Customer 	
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer cusObj = new Customer();
        cusObj.setPhoneNo("");
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            cusObj = buildCustomer(results);
                            //Association is to be build
                            stmt.close();
                            if(retrieveAssociation)

                            {   //The ZipCity object of customer has to be build as well

                                       

                            }
                            
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cusObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an customer object
	private Customer buildCustomer(ResultSet results)
      {   Customer cusObj = new Customer();
          try{
        	  cusObj.setfName(results.getString(1));
        	  cusObj.setlName(results.getString(2));
        	  cusObj.setAddress(results.getString(3));
        	  cusObj.setZipCode(results.getInt(4));
        	  cusObj.setPhoneNo(results.getString(5));
        	  
        	  
               
          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return cusObj;
      }
 
}  
    

